import re
from datetime import datetime, timedelta
from typing import List, Dict, Union, Optional, Tuple
import statistics
import os
import requests
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from collections import defaultdict

from sqlalchemy import func
from sqlalchemy import desc
import matplotlib.pyplot as plt

from .utils import (
    ProductEvaluationData,
    ProductEvaluationData,
    CumulativeProductEvaluationData,
    AverageProductEvaluationData,
    PointsOfSaleStatisticsData,
    ProductWithBadReviewsData,
    StatisticsData,
    TableData,
    StatisticsFilesData,
    EmailForMallingData,
    start_table_of_products_html,
    end_table_of_products_html
)
from utils import (
    get_list_of_links_of_prouducts_to_parsed,
    calculate_average_value
)
from db.main import session
from db.models import Product, PointsOfSale, Review
from db.utils import (
    get_average_points_of_sale_for_week,
    get_average_points_of_sale_for_previous_week,
    get_average_product_evaluation_for_day,
    get_impact_on_overvall_evaluation
)
import settings


def create_statistics_for_specified_products() -> None:
    """ Создаем статистику для указанных продуктов """

    # Создаем таблицу с дневными и накопительными оценками продуктов
    table_data: TableData = DailyAndCumulativeEvaluations() \
        .create_table_with_daily_and_cumulative_evaluations()
    GettingStatistics().create_table(
        table_data,
        table_name='daily_and_cumulative_evaluations',
    )

    # Создаем таблицу с точками продаж продуктов
    table_data: TableData = PointsOfSaleDataGeneration() \
        .get_data_with_points_of_sale_of_products()
    GettingStatistics().create_table(
        table_data,
        table_name='points_of_sale.png'
    )

    # Создаем таблицы с плохими отзывами
    product_with_bad_reviews: List[ProductWithBadReviewsData] = \
        DataGeneration().get_products_with_bad_review()
    ProductsWithBadReviewsDataGeneration().get_bad_product_reviews_in_form_table(
        product_with_bad_reviews
    )


class DataGeneration:
    """ Формируем данные """


    def categorize_products_into_groups(
        self,
        list_of_products: List[ProductEvaluationData]
    ) -> Dict[str, List[ProductEvaluationData]]:
        """ Разбиваем продукты по группам """

        grouped_products = defaultdict(list)

        for product in list_of_products:
            grouped_products[product['name']].append(product)

        return grouped_products


    def group_products_by_date(
        self,
        list_of_products: ProductEvaluationData,
    ) -> Dict:
        """ Группируем элементы продуктов по дате """

        grouped_products = defaultdict(list)

        for product in list_of_products:
            product_date = product['date'].strftime('%d-%m-%Y')
            grouped_products[product_date].append(product)

        return dict(grouped_products)


    def get_cumulative_evaluation_of_products(
        self,
        list_of_products: List[ProductEvaluationData]
    ) -> List[CumulativeProductEvaluationData]:
        """ Получаем накопительную оценку продуктов """

        def get_cumulative_evaluation(product) -> float:
            """ Добавляем накопительную оценку """

            cumulative_evaluation = 0.0

            for product in list_of_products:
                if isinstance(product['evaluation'], str): continue
                cumulative_evaluation += product['evaluation']

            return round(cumulative_evaluation, 2)


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        result = []
        grouped_products = self.categorize_products_into_groups(
            list_of_products
        )

        for list_of_products in grouped_products.values():
            cumulative_product_evaluation = CumulativeProductEvaluationData(
                list_of_product_evaluation=list_of_products,
                grand_total=get_cumulative_evaluation(list_of_products)
            )
            result.append(cumulative_product_evaluation)

        return result


    def get_avarage_product_evaluation(
        self,
        list_of_products: List[ProductEvaluationData]
    ) -> List[AverageProductEvaluationData]:
        """ Получаем среднюю оценку продуктов """

        def get_average_evaluation(list_of_products) -> float:
            """ Получаем среднюю оценку """

            product_evaluations = []
            for product in list_of_products:
                if isinstance(product['evaluation'], str): continue
                product_evaluations.append(product['evaluation'])

            return calculate_average_value(product_evaluations, 2)


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        result = []
        grouped_products = self.categorize_products_into_groups(
            list_of_products
        )

        for list_of_products in grouped_products.values():
            average_product_evaluation = AverageProductEvaluationData(
                list_of_product_evaluation=list_of_products,
                grand_total=get_average_evaluation(list_of_products)
            )
            result.append(average_product_evaluation)

        return result


    def get_points_of_sale_statistics(
        self, 
        list_of_products: List[ProductEvaluationData]
    ) -> List[PointsOfSaleStatisticsData]:
        """ Получаем продажи продукта """

        def get_points_of_sale_of_product(list_of_products) -> int:
            """ Получаем точки продаж продукта """

            nonlocal total_number_of_points_of_sale
            points_of_sale_of_product = 0

            for product in list_of_products:
                if isinstance(product['number_of_points_of_sale'], str): continue
                total_number_of_points_of_sale += product['number_of_points_of_sale']
                points_of_sale_of_product += product['number_of_points_of_sale']

            return points_of_sale_of_product


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        result = []
        total_number_of_points_of_sale = 0
        grouped_products = self.categorize_products_into_groups(
            list_of_products
        )

        for list_of_products in grouped_products.values():
            points_of_sale_statistics = PointsOfSaleStatisticsData(
                list_of_product_evaluation=list_of_products,
                grand_total=get_points_of_sale_of_product(list_of_products),
                total_number_of_points_of_sale=total_number_of_points_of_sale
            )
            result.append(points_of_sale_statistics)

        return result


    def get_products_with_bad_review(self) -> List[ProductWithBadReviewsData]:
        """ Получаем продукты с 'плохими' отзывами """

        products_with_bad_review: List[ProductWithBadReviewsData] = []
        list_of_links_of_prouducts = get_list_of_links_of_prouducts_to_parsed()

        list_of_products: List[Product] = session.query(
            Product
        ).filter(
            Product.link.in_(list_of_links_of_prouducts),
        ).order_by(
            desc(Product.date)
        ).all()

        for product in list_of_products:
            product_reviews: List[Review] = product.reviews.filter(
                Review.evaluation<4
            ).order_by(
                desc(Review.date)
            ).all()
            product_with_bad_reviews = ProductWithBadReviewsData(
                product_name=product.name,
                date=product.date,
                list_of_reviews=product_reviews
            )
            products_with_bad_review.append(product_with_bad_reviews)

        sorted_products_with_bad_review: List[ProductWithBadReviewsData] = \
            sorted(
                products_with_bad_review,
                key=lambda product: product['date'],
            )
        return sorted_products_with_bad_review


    def get_specified_products(
        self,
        start_day: int = 100,
        reverse: Optional[bool] = False
    ) -> List[ProductEvaluationData]:
        """ Получаем список указанных продуктов """

        def get_products_from_database() -> List[Product]:
            """ Получаем продукты из базы данных """

            nonlocal start_day
            list_of_links_of_prouducts = get_list_of_links_of_prouducts_to_parsed()
            query = session.query(
                PointsOfSale.product_id,
                func.count(PointsOfSale.product_id).label('count')
            ).group_by(
                PointsOfSale.product_id
            ).subquery()

            list_of_products = session.query(
                Product, query.c.count
            ).join(
                query,
                query.c.product_id == Product.id,
            ).filter(
                Product.link.in_(list_of_links_of_prouducts),
                Product.date.between(datetime.now() - timedelta(days=start_day), datetime.now())
            ).order_by(
                desc(Product.date)
            ).all()
            return list_of_products

        def get_list_of_product_evaluation_data(
            list_of_products: List[Product]
        ) -> List[ProductEvaluationData]:
            """ Получаем список объкетов ProductEvaluationData """

            list_of_product_evaluation = []

            for product in list_of_products:
                product, number_of_points_of_sale = product
                product_evaluation_data = ProductEvaluationData(
                    date=product.date,
                    name=product.name,
                    evaluation=product.raiting,
                    number_of_points_of_sale=number_of_points_of_sale
                )
                list_of_product_evaluation.append(product_evaluation_data)

            return list_of_product_evaluation

        def add_empty_products(
            list_of_products: List[ProductEvaluationData]
        ) -> List[ProductEvaluationData]:
            """ Добавляем пустые продукты для корректного отображения таблицы """

            new_list_of_products = []
            list_of_real_products = [(product['name'], product['date'].strftime('%d-%m-%Y'))
                                 for product in list_of_products]
            list_of_all_dates = list(set([product['date'].strftime('%d-%m-%Y')
                                 for product in list_of_products]))

            for product in list_of_products:
                product_date = product['date'].strftime('%d-%m-%Y')
                for product_date_str in list_of_all_dates:

                    current_new_list_of_products = [(product['name'], product['date'].strftime('%d-%m-%Y'))
                                                    for product in new_list_of_products]
                    current_product = (product['name'], product_date_str)

                    if product_date == product_date_str and not current_product in current_new_list_of_products:
                        new_list_of_products.append(product)
                        continue

                    if current_product in list_of_real_products: continue
                    if current_product in current_new_list_of_products: continue

                    day, month, year = map(int, product_date_str.split('-'))
                    new_date = datetime(day=day, month=month, year=year)
                    empty_product = ProductEvaluationData(
                        name=product['name'],
                        date=new_date,
                        evaluation='-',
                        number_of_points_of_sale='-'
                    )
                    new_list_of_products.append(empty_product)

            return new_list_of_products


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        list_of_products = get_products_from_database()

        list_of_products = get_list_of_product_evaluation_data(list_of_products)
        list_of_products = add_empty_products(list_of_products)

        if reverse:
            sorted_list_of_products = sorted(
                list_of_products,
                key=lambda product: product['date'],
                reverse=True
            )
        else:
            sorted_list_of_products = sorted(
                list_of_products,
                key=lambda product: product['date']
            )
        return sorted_list_of_products


    def get_statistics_data(self) -> StatisticsData:
        """ Получаем статистику указанных продуктов """

        list_of_products = self.get_specified_products()
        cumulative_evaluation_of_products = self.get_cumulative_evaluation_of_products(
            list_of_products
        )
        avarage_product_evaluation = self.get_avarage_product_evaluation(
            list_of_products
        )
        points_of_sale_statistics = self.get_points_of_sale_statistics(
            list_of_products
        )
        products_with_bad_review = self.get_products_with_bad_review()

        statistics_data = StatisticsData(
            average_product_evaluations=avarage_product_evaluation,
            cumulative_product_evaluations=cumulative_evaluation_of_products,
            points_of_sale=points_of_sale_statistics,
            product_with_bad_reviews=products_with_bad_review
        )
        return statistics_data


class GettingStatistics(DataGeneration):
    """ Инструменты для получения статистики """

    def create_table(
        self,
        table_data: TableData,
        table_name: str,
        large_table_rows: Optional[bool] = False
    ) -> None:
        """ Создаем таблицу по соответствующим данным """

        def enlarge_row(cell) -> None:
            """ Увеличиваем строку """

            table_cells[cell].set_height(height_of_cells)
            table_cells[cell].set_fontsize(text_font_size)

            cell_text = table_cells[cell].get_text().get_text()
            cell_text = cell_text.replace(' ', '\n')

            table_cells[cell].get_text().set_text(cell_text)
            table_cells[cell].set_aa('center')


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        text_font_size: int = 8 if not large_table_rows else 6
        height_of_cells: float = 0.4 if not large_table_rows else 0.16

        figure, ax = plt.subplots()
        ax.axis('off')

        full_table_data = []
        full_table_data.append(table_data['header'])
        _ = [full_table_data.append(element) for element in table_data['body']]
        #full_table_data.append(table_data['footer'])

        table = ax.table(
            cellText=full_table_data,
            loc='center',
            cellLoc='center'
        )
        table.auto_set_font_size(False)
        table.set_fontsize(10)
        table.scale(1.0, 5.0)

        table_cells = table.get_celld()
        for cell in table_cells:
            table_cells[cell].set_height(0.05)
            table_cells[cell].set_width(0.3)

            if not cell[0] and not large_table_rows: # Если это первая строка
                enlarge_row(cell)
            elif large_table_rows:
                enlarge_row(cell)

        path_to_table = os.path.abspath(
            f'user_data/generated_statistics/tables/{table_name}'
        )
        plt.savefig(
            path_to_table,
            dpi=300,
            bbox_inches='tight'
        )


    def get_data_to_create_table(
            self,
            list_of_products: List[Union[
                CumulativeProductEvaluationData,
                AverageProductEvaluationData,
                PointsOfSaleStatisticsData
            ]],
            product_part_designation: str
        ) -> TableData:
        """ Получаем данные для создания таблицы """

        def add_content_to_header() -> None:
            """ Добавляем заголовок для таблицы """

            for list_of_product_data in list_of_products:
                list_of_product_data = list_of_product_data['list_of_product_evaluation']

                for product_evaluation in list_of_product_data:
                    product_name = product_evaluation['name']
                    if product_name in table_header: continue
                    table_header.append(product_name)


        def group_product_elements(
            list_of_products: Dict[str, List[int]],
            product_evaluation_data: ProductEvaluationData,
            product_part_designation: str
        ) -> None:
            """ Группируем элементы продуктов """

            product_evaluation = product_evaluation_data.get(
                product_part_designation
            )
            product_date = product_evaluation_data['date'].strftime('%d-%m-%Y')

            if not product_date in list_of_products:
                list_of_products[product_date] = [product_evaluation]
            else:
                list_of_products[product_date].append(product_evaluation)


        def add_content_to_body() -> None:
            """ Добавляем содержимое в body таблицы """

            grouped_list_of_products = {}

            for list_of_product_data in list_of_products:
                for product_evaluation in list_of_product_data['list_of_product_evaluation']:
                    group_product_elements(
                        grouped_list_of_products,
                        product_evaluation,
                        product_part_designation
                    )

            for element in grouped_list_of_products:
                product_data = grouped_list_of_products[element]
                product_data.insert(0, element)
                table_body.append(product_data)


        def add_content_to_footer() -> None:
            """ Добавляем содержимое в footer таблицы """

            nonlocal list_of_products

            for list_of_product_data in list_of_products:
                table_footer.append(
                    list_of_product_data.get('grand_total')
                )


        def add_total_column() -> None:
            """ Добавляем колонку с итоговыми значениями """

            nonlocal table_header
            nonlocal table_body
            nonlocal table_footer

            table_header.append('Итого')

            for statistics_for_day in table_body:

                statistics_for_day.append(
                    calculate_average_value(statistics_for_day[1:], 2)
                )

            table_footer.append(
                calculate_average_value(table_footer[1:], 2)
            )


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        table_header = ['Дата']
        table_body = []
        table_footer = ['Итого']

        add_content_to_header()
        add_content_to_body()
        add_content_to_footer()
        add_total_column()

        table_data = TableData(
            header=table_header,
            body=table_body,
            footer=table_footer
        )

        return table_data


    def get_cumulative_evaluation_of_products_in_form_of_table(
        self,
        cumulative_product_evaluations: List[CumulativeProductEvaluationData]
    ) -> None:
        """ Получаем накопительную оценку продуктов в виде таблицы """

        table_data = self.get_data_to_create_table(
            cumulative_product_evaluations,
            product_part_designation='evaluation'
        )
        self.create_table(
            table_data,
            table_name='cumulative_evaluation.png'
        )


    def get_average_evaluation_of_products_in_form_of_table(
        self,
        average_product_evaluations: List[AverageProductEvaluationData]
    ) -> None:
        """ Получаем среднюю оценку продуктов в виде таблицы """

        table_data = self.get_data_to_create_table(
            average_product_evaluations,
            product_part_designation='evaluation'
        )
        self.create_table(
            table_data,
            table_name='average_evaluation.png'
        )


    def get_points_of_sales_of_product_in_form_of_table(
        self,
        points_of_sale: List[PointsOfSaleStatisticsData]
    ) -> None:
        """ Получаем точки продаж продукта в виде таблицы """

        table_data = self.get_data_to_create_table(
            points_of_sale,
            product_part_designation='number_of_points_of_sale'
        )
        self.create_table(
            table_data,
            table_name='number_of_points_of_sale.png'
        )


    def create_statistics_for_specified_products(self) -> None:
        """ Создаем статистику для укзанных продуктов """

        statistics_data = self.get_statistics_data()

        self.get_cumulative_evaluation_of_products_in_form_of_table(
            statistics_data['cumulative_product_evaluations']
        )
        self.get_average_evaluation_of_products_in_form_of_table(
            statistics_data['average_product_evaluations']
        )
        self.get_points_of_sales_of_product_in_form_of_table(
            statistics_data['points_of_sale']
        )
        self.get_bad_product_reviews_in_form_table(
            statistics_data['product_with_bad_reviews']
        )


class DailyAndCumulativeEvaluations(DataGeneration):
    """ Создаем таблицу с дневными и накопительными оценками """

    def add_content_to_header(
        self,
        table_header: List[str],
        table_data_one: TableData,
        table_data_two: TableData
    ) -> None:
        """ Добавляем содержимое в заголовок таблицы """

        data: List[TableData] = [table_data_one, table_data_two]

        for element in data:
            _ = [table_header.append(item) for item in element['header']]


    def add_content_to_body(
        self,
        table_body: List[str],
        table_data_one: TableData,
        table_data_two: TableData
    ) -> TableData:
        """ Добавляем содержимое в тело таблицы """

        for element_one, element_two in zip(table_data_one['body'], table_data_two['body']):
            new_element = element_one + element_two
            table_body.append(new_element)


    def create_table_with_daily_and_cumulative_evaluations(self) -> TableData:
        """ Создаем таблицу с дневными и накопительными оценками """

        table_header: List[str] = []
        table_body = []

        table_data_one: TableData = AverageProductEvaluationGeneration() \
            .get_data_with_average_evaluations_for_day()

        table_data_two: TableData = CumulativeProductEvaluationsGeneration() \
            .get_data_with_daily_product_evaluations()

        self.add_content_to_header(table_header, table_data_one, table_data_two)
        self.add_content_to_body(table_body, table_data_one, table_data_two)

        table_data = TableData(
            header=table_header,
            body=table_body
        )
        return table_data


class DifferenceBetweenAverageCumulativeDataGeneration(DataGeneration):
    """
    Формируем данные связанные с разницой средних оценок за день
    и средних накопительных
    """

    def add_content_to_table_header(
        self,
        table_header: List[str],
        ) -> None:
        """ Добавляем содержимое в заголовок таблицы """

        table_header.append('Дата')
        table_header.append('Продукт')
        table_header.append('СРЕДНЕЕ за день')
        table_header.append('СРЕДНЕЕ накопительно')
        table_header.append('diff')

    def add_content_to_table_body(
        self,
        table_body: List[List[Union[int, float]]],
        grouped_products: Dict[str, List[Dict]]
    ) -> None:
        """ Добавляем содержимое в тело таблицы """

        for date in grouped_products:
            for product in grouped_products[date]:
                daily_average: Union[int, float] = get_average_product_evaluation_for_day(
                    session,
                    product['name'],
                    product['date']
                )
                if type(product['evaluation']) == str or type(daily_average) == str:
                    diff: str = '-'
                else:
                    diff: float = round(product['evaluation'] - daily_average, 2)

                content: List = [date, product['name'].replace(' ', '\n'), daily_average, product['evaluation'], diff]
                table_body.append(content)


    def get_necessary_data(self) -> TableData:
        """ Получаем необходимые данные """

        table_header: List[str] = []
        table_body: List[List[Union[int, float]]] = []

        list_of_products: List[ProductEvaluationData] = \
            self.get_specified_products(start_day=21, reverse=True)

        grouped_products: Dict[str, List[Dict]] = \
            self.group_products_by_date(list_of_products)

        self.add_content_to_table_header(table_header)
        self.add_content_to_table_body(table_body, grouped_products)

        table_data = TableData(
            header=table_header,
            body=table_body,
        )
        return table_data



class ProductsWithBadReviewsDataGeneration(DataGeneration):
    """ Формируем данные связанные с плохими отзывами продуктов """

    def get_html_for_table_of_bad_products(
        self,
        list_of_products: List[str]
    ) -> str:
        """ Получаем html для отображения таблицы плохих продуктов """

        list_of_products_html: str = ''

        for product in list_of_products:
            date: str = product[1].strftime('%d-%m-%Y')
            text = f"""
                <tr>
                    <td>{product[0]}</td>
                    <td>{date}</td>
                    <td>{product[2]}</td>
                </tr>
            """
            list_of_products_html += text

        final_html: str = \
            start_table_of_products_html + list_of_products_html + '</table>'
        final_html += end_table_of_products_html
        return final_html


    def get_bad_product_reviews_in_form_table(
        self,
        product_with_bad_reviews: List[ProductWithBadReviewsData]
    ) -> None:

        full_table_data: List[Union[str, datetime]] = []

        for product in product_with_bad_reviews:
            for review in product['list_of_reviews']:
                full_table_data.append(
                    [product['product_name'], review.date, review.description]
                )

        sorted_full_table_data = sorted(
            full_table_data,
            key=lambda element: element[1],
            reverse=True
        )
        html: str = self.get_html_for_table_of_bad_products(sorted_full_table_data)
        path_to_table = os.path.abspath(
            f'user_data/generated_statistics/tables/table_of_bad_reviews.html'
        )
        with open(path_to_table, 'w') as file: file.write(html)


class AverageProductEvaluationGeneration(DataGeneration):
    """ Формируем данные связанные со средними оценками продуктов """

    def get_data_with_average_evaluations_for_day(self) -> TableData:
        """
        Получаем данные для создания таблицы со средними оценками
        продуктов за день
        """

        def add_content_to_table_header() -> None:
            """ Добавляем содержимое в заголовок таблицы """

            nonlocal table_header
            nonlocal list_of_products

            table_header.append('Дата')

            for date in grouped_products:
                for product in grouped_products[date]:
                    product_name: str = product['name']
                    if product_name in table_header: continue
                    table_header.append(product['name'])

            additional_columns: List[str] = [
                'СРЕДНЕЕ за день',
                'СРЕДНЕЕ накопительно',
                'diff'
            ]
            _ = [table_header.append(element) for element in additional_columns]


        def add_content_to_table_body() -> None:
            """ Добавляем содержимое в тело таблицы """

            for date in grouped_products:
                list_of_evaluations: List = []
                for product in grouped_products[date]:
                    daily_average: Union[int, float] = get_average_product_evaluation_for_day(
                        session,
                        product['name'],
                        product['date']
                    )
                    list_of_evaluations.append(daily_average)

                additional_columns: Tuple[float] = \
                    get_impact_on_overvall_evaluation(
                        session,
                        date,
                        list_of_evaluations
                    )
                _ = [list_of_evaluations.append(value) for value in additional_columns]
                list_of_evaluations.insert(0, date)
                table_body.append(list_of_evaluations)


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        table_header: List[str] = []
        table_body: List[Union[int, float]] = []

        list_of_products: List[ProductEvaluationData] = \
            self.get_specified_products(reverse=True)

        grouped_products: Dict[str, List[Dict]] = \
            self.group_products_by_date(list_of_products)

        add_content_to_table_header()
        add_content_to_table_body()

        table_data = TableData(
            header=table_header,
            body=table_body,
        )
        return table_data


class CumulativeProductEvaluationsGeneration(DataGeneration):
    """ Формируем данные связанные с накопительными оценками продуктов """

    def get_data_with_daily_product_evaluations(self) -> TableData:
        """
        Получаем данные для создания таблицы с дневными оценками продуктов
        """

        def add_content_to_table_header() -> None:
            """ Добавляем содержимое в заголовок таблицы """

            nonlocal table_header
            nonlocal list_of_products

            table_header.append('Дата')

            for date in grouped_products:
                for product in grouped_products[date]:
                    product_name: str = product['name']
                    if product_name in table_header: continue
                    table_header.append(product['name'])

            table_header.append('СРЕДНЕЕ накопительно')

        def add_content_to_table_body() -> None:
            """ Добавляем содержимое в тело таблицы """

            for date in grouped_products:
                list_of_evaluations: List[Union[int, float, str]] = \
                    [product['evaluation'] for product in grouped_products[date]]

                average_cumulative: float = calculate_average_value(list_of_evaluations, 2)
                list_of_evaluations.append(average_cumulative)
                list_of_evaluations.insert(0, date)
                table_body.append(list_of_evaluations)


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        table_header: List[str] = []
        table_body: List[List[Union[int, float]]] = []

        list_of_products: List[ProductEvaluationData] = \
            self.get_specified_products(reverse=True)

        grouped_products: Dict[str, List[Dict]] = \
            self.group_products_by_date(list_of_products)

        add_content_to_table_header()
        add_content_to_table_body()

        table_data = TableData(
            header=table_header,
            body=table_body,
        )
        return table_data


class PointsOfSaleDataGeneration(DataGeneration):
    """ Формируем данные связанные с точками продаж """

    def get_data_with_points_of_sale_of_products(self) -> TableData:
        """ Получаем данные для создания таблицы с точками продаж продуктов """

        def add_content_to_table_header() -> None:
            """ Добавляем содержимое в заголовок таблицы """

            nonlocal table_header
            nonlocal list_of_products

            table_header.append('Дата')

            for date in grouped_products:
                for product in grouped_products[date]:
                    product_name: str = product['name']
                    if product_name in table_header: continue
                    table_header.append(product['name'])

        def add_average_for_weeks(function, label) -> None:
            """
            Добавляем среднее количество точек продаж за какую-то неделю
            """

            for date in grouped_products:
                list_of_average_quantities: List[Union[int, float]] = []
                for product in grouped_products[date]:
                    average_number = function(
                        session,
                        datetime.now(),
                        product['name']
                    )
                    list_of_average_quantities.append(average_number)

                list_of_average_quantities.insert(0, label)
                table_body.append(list_of_average_quantities)
                break

        def add_product_dynamics() -> None:
            """ Добавляем динамику продукта """

            product_dynamics: List[Union[str, int]] = []
            statistics_for_week = \
                [row for row in table_body if row[0] == 'СРЕДНЕЕ за 7 дней'][0][1:]
            statistics_for_previous_week = \
                [row for row in table_body if row[0] == 'ПРЕДЫДУЩИЕ 7 дней'][0][1:]
            data = [statistics_for_week, statistics_for_previous_week]

            for statistic_for_week, statistic_for_previous_week in zip(*data):
                if statistics_for_week and statistic_for_previous_week:
                    value: int = int(round(
                        (statistic_for_week / statistic_for_previous_week) * 100, 2
                    ))
                    value: str = f'{value}%'
                    product_dynamics.append(value)
                else:
                    product_dynamics.append(0)

            product_dynamics.insert(0, 'Динамика')
            table_body.append(product_dynamics)

        def add_average_for_week() -> None:
            """ Добавляем среднее значение для колонки 'Среднее за 7 дней' """

            for row in table_body:
                if row[0] == 'СРЕДНЕЕ за 7 дней':
                    row.append(
                        calculate_average_value(row[1:], 2)
                    )

        def add_average_for_previous_week() -> None:
            """ Добавляем среднее значение для колонки 'Предыдущие 7 дней' """

            for row in table_body:
                if row[0] == 'ПРЕДЫДУЩИЕ 7 дней':
                    row.append(
                        calculate_average_value(row[1:], 2)
                    )

        def add_average_for_dynamics() -> None:
            """ Добавляем среднее значение для поля 'Динамика' """

            statistics_for_week = \
                [row for row in table_body if row[0] == 'СРЕДНЕЕ за 7 дней'][0][-1]
            statistics_for_previous_week = \
                [row for row in table_body if row[0] == 'ПРЕДЫДУЩИЕ 7 дней'][0][-1]

            if statistics_for_previous_week:
                value: int = int(round(
                    (statistics_for_week / statistics_for_previous_week) * 100, 0
                ))
            else:
                value: int = 0

            value: str = f'{value}%'

            for row in table_body:
                if row[0] == 'Динамика':
                    row.append(value)

        def get_column_number_avg() -> int:
            """ Получаем номер колонки 'Среднее' """

            result: int = \
                [table_header.index(element) for element in table_header if element == 'СРЕДНЕЕ'][0]
            return result


        def add_max_of_week() -> None:
            """ Добавляем максимальное значение точек продаж за неделю """

            for row in table_body:
                if row[0] == 'СРЕДНЕЕ за 7 дней':
                    row.append(
                        int(round(max(row[1:]), 0))
                    )

        def add_max_of_previous_week() -> None:
            """ Добавляем максимальное значение точек продаж за предыдущую неделю """

            for row in table_body:
                if row[0] == 'ПРЕДЫДУЩИЕ 7 дней':
                    row.append(
                        int(round(max(row[1:]), 0))
                    )

        def add_dinamics_with_max_values() -> None:
            """ Добавляем динамику для полей с максимальными значениями """

            statistics_for_week = \
                [row for row in table_body if row[0] == 'СРЕДНЕЕ за 7 дней'][0][-1]
            statistics_for_previous_week = \
                [row for row in table_body if row[0] == 'ПРЕДЫДУЩИЕ 7 дней'][0][-1]

            if statistics_for_previous_week:
                value: int = int(round(
                    (statistics_for_week / statistics_for_previous_week) * 100, 0
                ))
            else:
                value: int = 0

            value: str = f'{value}%'

            for row in table_body:
                if row[0] == 'Динамика':
                    row.append(value)

        def add_products_to_body() -> None:
            """ Добавляем продукты в тело таблицы """

            nonlocal list_of_products
            nonlocal table_body
            group_products: Dict[str, List[Dict]] = \
                self.group_products_by_date(list_of_products)

            for date in group_products:
                points_of_sale_per_day: List[Union[str, int]] = []
                for product in group_products[date]:
                    points_of_sale_per_day.append(
                        product['number_of_points_of_sale']
                    )
                points_of_sale_per_day.insert(0, date)
                table_body.append(points_of_sale_per_day)

        def add_average_for_points_of_sale_of_products() -> None:
            """ Добавляем среднее значение для точек продаж продуктов """

            nonlocal table_body

            for row in table_body:
                if re.findall('\d+?-\d+?-\d+', row[0]):
                    value: int = int(calculate_average_value(row[1:], 0))
                    row.append(value)

        def add_max_for_points_of_sale_of_products() -> None:
            """ Добавляем максимальное значение для точек продаж продуктов """

            nonlocal table_body

            for row in table_body:
                if re.findall('\d+?-\d+?-\d+', row[0]):
                    values: List[int] = [element for element in row[1:] if type(element) in [int, float]]
                    value: int = max(values)
                    row.append(value)

        def add_avg_max_for_week(week) -> None:
            """ Добавляем среднее/максимальное значение за неделю в % """

            statistics = [row for row in table_body if row[0] == week][0][-2:]

            for row in table_body:
                if not row[0] == week: continue
                if 0 in statistics:
                    row.append(0)
                    continue

                value: int = int(round(
                    (statistics[0] / statistics[1]) * 100, 0
                ))
                row.append(value)

        def add_dinamycs_of_avg_max_value_to_products() -> None:
            """
            Добавляем значение в колонку '%(итого среднее/максимум) в день
            для каждого продукта
            """

            nonlocal table_body

            for row in table_body:
                if re.findall('\d+?-\d+?-\d+', row[0]):
                    avg, max = row[-2:]
                    value: int = f'{int(round((avg / max) * 100, 0))}%'
                    row.append(value)

        def add_max_for_entire_period(week: str) -> None:
            """ Добавляем максимум за весь период для недели """

            nonlocal grouped_products
            nonlocal table_body

            points_of_sale: int = [product['number_of_points_of_sale'] \
                                   for element in list(grouped_products.values()) \
                                   for product in element if type(product['number_of_points_of_sale']) == int]

            for row in table_body:
                if not row[0] == week: continue
                row.append(max(points_of_sale))

        def add_max_for_entire_period_for_products() -> None:
            """ Добавляем максимум за весь период для каждого продукта """

            nonlocal grouped_products
            nonlocal table_body
            points_of_sale: int = [product['number_of_points_of_sale'] \
                                   for element in list(grouped_products.values()) \
                                   for product in element if type(product['number_of_points_of_sale']) == int]
            max_points_of_sale: int = max(points_of_sale)

            for row in table_body:
                if re.findall('\d+?-\d+?-\d+', row[0]):
                    row.append(max_points_of_sale)

        def add_avg_max_for_entire_period(week) -> None:
            """ Добавляем среднее/максимум за весь период для недели """
            
            nonlocal column_number_avg

            for row in table_body:
                if not row[0] == week: continue
                one: int = int(row[column_number_avg])
                two: int = int(row[-1])
                value: int = int(round(
                    (one / two) * 100, 0
                ))
                value: str = f'{value}%'
                row.append(value)

        def get_number_from_procentage(value) -> None:
            """ Получаем число из процента """
            try:
                return int(re.findall('\d+', value)[0])
            except TypeError:
                return value

        def add_dynamics_for_latest_values() -> None:
            """ Добавляем динамику для последних значений """

            nonlocal table_body

            statistics_for_week = \
                [row for row in table_body if row[0] == 'СРЕДНЕЕ за 7 дней'][0][-1]
            statistics_for_previous_week = \
                [row for row in table_body if row[0] == 'ПРЕДЫДУЩИЕ 7 дней'][0][-1]

            divisible, divider = list(map(
                get_number_from_procentage,
                [statistics_for_week, statistics_for_previous_week]
            ))

            if divisible and divider:
                value: int = int(round(
                    (divisible / divider) * 100, 2
                ))
                value: str = f'{value}%'
            else:
                value: int = 0

            for row in table_body:
                if not row[0] == 'Динамика': continue
                row.append(value)

        def add_avg_max_for_entire_period_to_products():
            """ Добавляем среднее/максимум для продуктов за весь период """

            nonlocal table_body
            nonlocal column_number_avg

            for row in table_body:
                if re.findall('\d+?-\d+?-\d+', row[0]):
                    value: int = int(round(
                        (row[column_number_avg] / row[-1]) * 100
                    ))
                    value: str = f'{value}%'
                    row.append(value)


        # ВЫШЕ ОПРЕДЕЛЕНИЕ ФУНКЦИЙ

        table_header: List[str] = []
        table_body: List[List[Union[int, float]]] = []

        list_of_products: List[ProductEvaluationData] = \
            self.get_specified_products(start_day=21, reverse=True)

        grouped_products: Dict[str, List[Dict]] = \
            self.group_products_by_date(list_of_products)

        add_content_to_table_header()

        table_header.append('СРЕДНЕЕ')
        add_average_for_weeks(
            get_average_points_of_sale_for_week,
            'СРЕДНЕЕ за 7 дней'
        )
        add_average_for_weeks(
            get_average_points_of_sale_for_previous_week,
            'ПРЕДЫДУЩИЕ 7 дней'
        )
        add_product_dynamics()
        add_average_for_week()
        add_average_for_previous_week()
        add_average_for_dynamics()
        column_number_avg: int = get_column_number_avg()

        table_header.append('МАКСИМУМ')
        add_max_of_week()
        add_max_of_previous_week()
        add_dinamics_with_max_values()

        add_products_to_body()
        add_average_for_points_of_sale_of_products()
        add_max_for_points_of_sale_of_products()

        table_header.append('%(итого среднее/максимум) в день')
        add_avg_max_for_week('СРЕДНЕЕ за 7 дней')
        add_avg_max_for_week('ПРЕДЫДУЩИЕ 7 дней')
        add_dynamics_for_latest_values()
        add_dinamycs_of_avg_max_value_to_products()

        table_header.append('Максимум за весь период')
        add_max_for_entire_period('СРЕДНЕЕ за 7 дней')
        add_max_for_entire_period('ПРЕДЫДУЩИЕ 7 дней')
        add_dynamics_for_latest_values()
        add_max_for_entire_period_for_products()

        table_header.append('%(итого среднее/максимум) за весь период')
        add_avg_max_for_entire_period('СРЕДНЕЕ за 7 дней')
        add_avg_max_for_entire_period('ПРЕДЫДУЩИЕ 7 дней')
        add_dynamics_for_latest_values()
        add_avg_max_for_entire_period_to_products()

        table_data = TableData(
            header=table_header,
            body=table_body,
        )
        return table_data


class SendingStatistics:
    """ Инструменты для отправки статистики """

    def get_statistics_files_data(self) -> StatisticsFilesData:
        """ Получаем объект с файлами статистики """

        statistics_file = {}
        path_to_file = os.path.abspath(
            'user_data/generated_statistics/tables'
        )
        files_with_statistics = [
            [
                'points_of_sale',
                'Точки продаж продуктов'
            ],
            [
                'table_of_bad_reviews',
                'Продукты с "плохими" отзывами'
            ],
            [
                'daily_and_cumulative_evaluations',
                'Дневные и накопительные оценки продуктов'
            ]
        ]

        for element in files_with_statistics:

            file_name, annotation = element

            if file_name == 'table_of_bad_reviews':
                extension = '.html'
            else:
                extension = '.png'

            file_name = f'{file_name}{extension}'
            file_path = path_to_file + f'/{file_name}'
            statistics_file[file_name] = (file_path, annotation, file_name)

        statistics_files_data = StatisticsFilesData(**statistics_file)
        return statistics_files_data


    def send_statistics_to_telegram(
        self,
        statistics_files_data: StatisticsFilesData
    ) -> None:
        """ Отправляем статистику в телеграм """

        request = 'https://api.telegram.org/bot{}/sendDocument'.format(
            settings.TELEGRAM_BOT_TOKEN
        )

        for element in statistics_files_data.values():
            file_path, annotation, file_name = element
            data = {
                'chat_id': settings.TELEGRAM_CHAT_ID,
                'caption': annotation
            }
            with open(file_path, 'rb') as file:
                files = {'document': file}
                requests.post(request, data=data, files=files)


    def get_email_for_malling_data(self) -> EmailForMallingData:
        """ Получаем список email для рассылки """

        file_path = os.path.abspath('user_data/malling_by_email.txt')
        with open(file_path) as file:
            file_contents = file.read().strip()
            file_contents = file_contents.split(
                '-----------------------------'
            )[1].strip()
            sender, recipients = file_contents.split('\n\n')

            senders_email_name, senders_email_password = sender.split('\n')
            list_of_emails_for_malling = recipients.split('\n')
            return EmailForMallingData(
                senders_email_name=senders_email_name,
                senders_email_password=senders_email_password,
                list_of_emails_for_malling=list_of_emails_for_malling
            )


    def send_statistics_by_email(
        self,
        statistics_files_data: StatisticsFilesData
    ) -> None:
        """ Отправляем статистику на почту """

        email_for_malling_data = self.get_email_for_malling_data()
        server = smtplib.SMTP('smtp.mail.ru', 587)
        server.starttls()
        server.login(
            email_for_malling_data['senders_email_name'],
            email_for_malling_data['senders_email_password'],
        )

        for recipients in email_for_malling_data['list_of_emails_for_malling']:
            for file_path, annotation, file_name in statistics_files_data.values():

                message = MIMEMultipart()
                message.attach(MIMEText(annotation))
                message['from'] = email_for_malling_data['senders_email_name']
                message['to'] = recipients
                message['Subject'] = 'Статистика данных по парсингу vkusvill'

                if '.html' in file_name:
                    with open(file_path, encoding='utf8', errors='ignore') as file:
                        file_to_send = MIMEText(file.read())
                else:
                    with open(file_path, 'rb') as file:
                        file_to_send = MIMEImage(file.read())

                file_to_send.add_header(
                    'content-disposition',
                    'attachment',
                    filename=file_name
                )
                message.attach(file_to_send)
                server.sendmail(
                    email_for_malling_data['senders_email_name'],
                    recipients,
                    message.as_string()
                )


    def send_statistics_of_specified_products(self) -> None:
        """ Отправляем статистику """

        statistics_files_data = self.get_statistics_files_data()
        self.send_statistics_to_telegram(statistics_files_data)
        self.send_statistics_by_email(statistics_files_data)
